import xlrd,xlsxwriter,os
ions=['ZINC ION','COBALT (II) ION','MERCURY (II) ION',\
      'NICKEL (II) ION','COBALT (III) ION','MANGANESE (II) ION',\
      'COPPER (II) ION','LEAD (II) ION','FE (III) ION',\
      'SILVER ION','GOLD ION','FE (II) ION',\
      'GOLD 3+ ION','COPPER (I) ION','ALUMINUM ION',\
      'TUNGSTEN ION','PLATINUM (IV) ION','PLATINUM (II) ION',\
      'CHLORIDE ION','PHOSPHATE ION','IODIDE ION',\
      'BROMIDE ION','HYDROXIDE ION','FLUORIDE ION',\
      'UNKNOWN ATOM OR ION']
ligandlist=[]
def screen(tab,result):
    data=xlrd.open_workbook(tab);sheet=data.sheet_by_index(0)
    wb=xlsxwriter.Workbook(result);ws=wb.add_worksheet()
    ws.write_row(0,0,sheet.row_values(0))
    entry=sheet.cell(1,0).value
    method=sheet.cell(1,1).value
    ligand=sheet.cell(1,2).value
    title=sheet.cell(1,4).value
    def check(item,c):
        global content
        if sheet.cell(i,c).value != '':
            content=sheet.cell(i,c).value
        else:
            content=item
        ws.write(i,c,content)
    for i in range(1,sheet.nrows):
        name=sheet.cell(i,6).value
        structure=sheet.cell(i,7).value
        weight=sheet.cell(i,5).value
        if name.endswith('ION'):
            if 'IUM' in name or name in ions:
                name='';structure='';weight=''
        check(entry,0);check(entry,3);entry=content
        check(method,1);method=content
        check(ligand,2);ligand=content
        check(title,4);title=content
        ws.write(i,5,weight)
        ws.write(i,6,name)
        ws.write(i,7,structure)
    wb.close()
def clear(tabin,tabout):
    data=xlrd.open_workbook(tabin);sheet=data.sheet_by_index(0)
    wb=xlsxwriter.Workbook(tabout);ws=wb.add_worksheet()
    ws.write_row(0,0,sheet.row_values(0))
    k=1
    entrylist=[]
    for i in range(1,sheet.nrows):
        name=sheet.cell(i,6).value
        if name == '':
            continue
        else:
            entry=sheet.cell(i,0).value
            if entry not in entrylist:
                ws.write(k,0,entry)
                entrylist.append(entry)
            for j in range(1,8):
                ws.write(k,j,sheet.cell(i,j).value)
            k+=1
    wb.close()
    os.remove(tabin)
for i in range(1,7):
    src='tab'+str(i)+'.xlsx'
    mid=str(i)+'.xlsx'
    tgf='tab'+str(i)+'_screen.xlsx'
    screen(src,mid)
    clear(mid,tgf)
